﻿BEGIN TRY
	BEGIN TRAN

	MERGE [dbo].[TransformationType] AS TGT 
	USING
	(
		SELECT -1, 'Unknown' UNION ALL
		SELECT 1, 'Key' UNION ALL
		SELECT 2, 'Regular Transformation' UNION ALL
		SELECT 3, 'System Transformation'
	) AS SRC ([TransformationTypeId], [TransformationTypeName])
	ON TGT.[TransformationTypeId] = SRC.[TransformationTypeId]  

	WHEN MATCHED THEN   
	UPDATE SET
		[TransformationTypeName] = SRC.[TransformationTypeName]

	WHEN NOT MATCHED BY TARGET THEN  
		INSERT ([TransformationTypeId], [TransformationTypeName])
		VALUES (SRC.[TransformationTypeId], SRC.[TransformationTypeName])

	WHEN NOT MATCHED BY SOURCE THEN  
		DELETE;

	COMMIT TRAN;
END TRY
BEGIN CATCH
	ROLLBACK TRAN;
END CATCH
